using System;
using System.Data;
using System.Security.Permissions;

namespace Caam.Framework.Common
{
    //[StrongNameIdentityPermission(SecurityAction.LinkDemand, PublicKey = PublicKeys.MyCompany)]
    public sealed class DbManager : IDbManager, IDisposable
    {
        private IDbConnection idbConnection;
        private IDataReader idataReader;
        private IDbCommand idbCommand;
        private DataProvider providerType;
        private IDbTransaction idbTransaction = null;
        private IDbDataParameter[] idbParameters = null;
        private string strConnection;

        public DbManager():this( DataProvider.SqlServer)
        {            
        }

        public DbManager(DataProvider providerType)
        {
            this.MustDispose = true;
            this.providerType = providerType;
            this.idbCommand = DbManagerFactory.GetCommand(this.ProviderType);
            this.ConnectionString = ConnectionProvider.Get_Instance().GetConnString("");
        }

        public DbManager(string dataProvider)
        {
            this.MustDispose = true;
            // Return the requested DaoFactory
            switch (dataProvider)
            {
                //case "System.Data.OleDb": return new Access.AccessDaoFactory();
                case "System.Data.SqlClient":
                    //Debug.WriteLine("System.Data.SqlClient");
                    this.providerType = DataProvider.SqlServer;
                    break;
                case "System.Data.OracleClient":
                    //Debug.WriteLine("System.Data.OracleClient");
                    this.providerType = DataProvider.Oracle;
                    break;
                // Just in case: the Design Pattern Framework always has MS Access available.
                default:
                    this.providerType = DataProvider.SqlServer;// new Access.AccessDaoFactory();
                    break;
            }
        }

        public DbManager(DataProvider providerType, string connectionString)
        {
            this.MustDispose = true;
            this.providerType = providerType;
            this.strConnection = connectionString;
        }

        public IDbConnection Connection
        {
            get
            {
                return idbConnection;
            }
        }

        public bool MustDispose { get; set; }

        public IDataReader DataReader
        {
            get
            {
                return idataReader;
            }
            set
            {
                idataReader = value;
            }
        }

        public DataProvider ProviderType
        {
            get
            {
                return providerType;
            }
            set
            {
                providerType = value;
            }
        }

        public string ConnectionString
        {
            get
            {
                return strConnection;
            }
            set
            {
                strConnection = value;
            }
        }

        public IDbCommand Command
        {
            get
            {
                return idbCommand;
            }
        }

        public IDbTransaction Transaction
        {
            get
            {
                return idbTransaction;
            }
        }

        public IDbDataParameter[] Parameters
        {
            get
            {
                return idbParameters;
            }
        }

        public void Open()
        {
            idbConnection = DbManagerFactory.GetConnection(this.providerType);
            if (this.ConnectionString == string.Empty)
                throw new ArgumentNullException("Connection string is empty");
            idbConnection.ConnectionString = this.ConnectionString;
            if (idbConnection.State != ConnectionState.Open)
                idbConnection.Open();
            //this.idbCommand = DbManagerFactory.GetCommand(this.ProviderType);
        }

        public void Close()
        {
            if (idbConnection != null && idbConnection.State != ConnectionState.Closed)
                idbConnection.Close();
            if (idbConnection != null)
                ((IDisposable)idbConnection).Dispose();
        }

        public void Dispose()
        {
            //GC.SuppressFinalize(this);
            this.Close();
            this.idbCommand = null;
            this.idbTransaction = null;
            this.idbConnection = null;
            //this.idbParameters = null;
            this.ConnectionString = null;
            //GC.Collect();
            //GC.WaitForPendingFinalizers();
        }

        public void CreateParameters(int paramsCount)
        {
            idbParameters = new IDbDataParameter[paramsCount];
            idbParameters = DbManagerFactory.GetParameters(this.ProviderType, paramsCount);
        }

        public void AddParameter(int index, string paramName, object objValue)
        {
            if (index < idbParameters.Length)
            {

                if (this.providerType == DataProvider.Oracle)
                    idbParameters[index].ParameterName = paramName.Replace("@", "");
                else
                    idbParameters[index].ParameterName = paramName;
                idbParameters[index].Direction = ParameterDirection.Input;
                idbParameters[index].Value = objValue;
            }
        }

        public object GetParameterValue(string paramName)
        {
            if (idbParameters == null)
                throw new Exception("idbParameters is null");

            int len = idbParameters.Length;
            IDbDataParameter param;
            for (int i = 0; i < len; i++)
            {
                param = idbParameters[i];
                if (param.ParameterName == paramName)
                    return param.Value;
            }
            return null;
        }

        public void AddParameter(int index, IDbDataParameter param)
        {
            if (index < idbParameters.Length)
            {
                idbParameters[index] = param;
            }
        }

        public void AddOutParameter(int index, string paramName, object type)
        {
            if (index < idbParameters.Length)
            {
                idbParameters[index].ParameterName = paramName;
                idbParameters[index].Direction = ParameterDirection.Output;
                idbParameters[index].Size = 8;
            }
        }

        public void BeginTransaction()
        {
            if (this.idbConnection != null && this.idbConnection.State == ConnectionState.Open)
                this.idbTransaction = this.idbConnection.BeginTransaction();
            if (this.idbTransaction == null)
                idbTransaction = DbManagerFactory.GetTransaction(this.ProviderType);
            this.idbCommand.Transaction = idbTransaction;
        }

        public void CommitTransaction()
        {
            if (this.idbTransaction != null)
                this.idbTransaction.Commit();
            idbTransaction = null;
        }

        public void RollbackTransaction()
        {
            if (this.idbTransaction != null)
                this.idbTransaction.Rollback();
            idbTransaction = null;
        }


        public IDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            this.idbCommand = DbManagerFactory.GetCommand(this.ProviderType);
            idbCommand.Connection = this.Connection;
            PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
            this.DataReader = idbCommand.ExecuteReader();
            idbCommand.Parameters.Clear();
            return this.DataReader;
        }

        public void CloseReader()
        {
            if (this.DataReader != null)
                this.DataReader.Close();
        }

        private void AttachParameters(IDbCommand command,
          IDbDataParameter[] commandParameters)
        {
            foreach (IDbDataParameter idbParameter in commandParameters)
            {
                if ((idbParameter.Direction == ParameterDirection.InputOutput)
                &&
                  (idbParameter.Value == null))
                {
                    idbParameter.Value = DBNull.Value;
                }
                command.Parameters.Add(idbParameter);
            }
        }

        public void AttachParameters(IDbDataParameter[] commandParameters)
        {
            CreateParameters(commandParameters.Length);

            //foreach (IDbDataParameter idbParameter in commandParameters)
            for (int i = 0; i < commandParameters.Length; i++)
            {
                //if ((idbParameter.Direction == ParameterDirection.InputOutput)
                //&&
                //  (idbParameter.Value == null))
                //{
                //    idbParameter.Value = DBNull.Value;
                //}
                //this.Command.Parameters.Add(idbParameter);
                this.AddParameter(i, commandParameters[i]);
            }
        }

        private void PrepareCommand(IDbCommand command, IDbConnection connection,
          IDbTransaction transaction, CommandType commandType, string commandText,
          IDbDataParameter[] commandParameters)
        {
            if (connection == null)
                throw new ArgumentNullException("Connection is null");
            command.Connection = connection;
            if (this.providerType == DataProvider.SqlServer)
                command.CommandText = commandText.Substring(commandText.IndexOf(".") + 1);
            else
                command.CommandText = commandText;
            command.CommandType = commandType;

            if (transaction != null)
            {
                command.Transaction = transaction;
            }

            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            //this.idbCommand = DbManagerFactory.GetCommand(this.ProviderType);
            this.Open();
            PrepareCommand(idbCommand, this.Connection, this.Transaction,
            commandType, commandText, this.Parameters);
            int returnValue = idbCommand.ExecuteNonQuery();
            idbCommand.Parameters.Clear();
            return returnValue;
        }

        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            //this.idbCommand = DbManagerFactory.GetCommand(this.ProviderType);
            this.Open();
            PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
            object returnValue = idbCommand.ExecuteScalar();
            idbCommand.Parameters.Clear();
            if (this.MustDispose)
                this.Dispose();
            return returnValue;
        }

        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            //System.Diagnostics.Trace.WriteLine("Begin..dataset"+DateTime.Now.ToString("hhMMss.fff"));
            //this.idbCommand = DbManagerFactory.GetCommand(this.ProviderType);
            DataSet dataSet = new DataSet();
            try
            {
                this.Open();
                PrepareCommand(this.idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
                IDbDataAdapter dataAdapter = DbManagerFactory.GetDataAdapter(this.ProviderType);
                dataAdapter.SelectCommand = this.idbCommand;
                
                dataAdapter.Fill(dataSet);
                idbCommand.Parameters.Clear();
                //System.Diagnostics.Trace.WriteLine("End..dataset" + DateTime.Now.ToString("hhMMss.fff"));
                return dataSet;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (this.MustDispose)
                    this.Dispose();
            }
            
        }

        public DataTable ExecuteDataTable(CommandType commandType, string commandText)
        {
            DataSet ds = ExecuteDataSet(commandType, commandText);           
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            return null;
        }

        public DataTable ExecuteDataTable(string commandText)
        {
            return ExecuteDataTable(CommandType.StoredProcedure, commandText);
        }

        #region version 2
        //public DataTable ExecuteDataTableV2(CommandType commandType, string commandText)
        //{
        //    DataSet ds = ExecuteDataSet(commandType, commandText);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    return null;
        //}

        public object ExecuteScalarV2(CommandType commandType, string commandText)
        {
            try
            {
                //this.idbCommand = DbManagerFactory.GetCommand(this.ProviderType);
                this.Open();
                PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);

                object returnValue = idbCommand.ExecuteScalar();
                return returnValue;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (this.MustDispose)
                    this.Dispose();
            }
        }

        /// <summary>
        /// ExecuteNonQueryV2 - lbnam04
        /// </summary>        
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public int ExecuteNonQueryV2(CommandType commandType, string commandText)
        {
            try
            {
                //this.idbCommand = DbManagerFactory.GetCommand(this.ProviderType);
                this.Open();
                PrepareCommand(idbCommand, this.Connection, this.Transaction,
                commandType, commandText, this.Parameters);

                int returnValue = idbCommand.ExecuteNonQuery();
                idbCommand.Parameters.Clear();
                return returnValue;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (this.MustDispose)
                    this.Dispose();
            }
        }
        #endregion
    }
}
